package com.minisoft.utils;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.file.FileReader;
import cn.hutool.core.thread.ThreadUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.Page;
import cn.hutool.db.PageResult;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;

import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ThreadPoolExecutor;

/**
 * @author JueSu
 * @description: 检查到访客户差异
 * @date 2021/11/3 9:44
 */
@Slf4j
public class FlushData {

    public static String DB_CENTER = "center-pro";
    public static String DB_YDAC = "ydac-pro";

    static String beginDate = "2021-11-07";

    public static void main(String[] args) throws SQLException {
        List<Entity> centerResult = Db.use(DB_CENTER).query("SELECT om.customer_id,m.mobile,o.sale_team_id,o.create_time,o.visit_first_date,o.update_time from customer_opportunity o ,customer_mobile m ,customer_opportunity_map om WHERE m.customer_opportunity_id = o.customer_opportunity_id and m.sort = 0 and om.opportunity_id = o.customer_opportunity_id and o.create_time > ?",beginDate);
        List<Entity> ydacResult = Db.use(DB_YDAC).query("select * from dh_crm_saleruser  WHERE createtime > ?",beginDate);
        System.out.println("centerResult:,ydacResult:"+centerResult.size()+","+ydacResult.size());
        Map<String,Entity> ydacMap = new HashMap<>();

        List<String> ydacLessUsers = new ArrayList<>();

        List<String> ydacUserSql = new ArrayList<>();
        List<String> qmyxUserSql = new ArrayList<>();
        List<String> qmyxUserFollowSql = new ArrayList<>();
        List<String> saleUserSql = new ArrayList<>();
        List<String> saleUserFollowSql = new ArrayList<>();

        ydacResult.forEach(item -> {
            ydacMap.put(item.getStr("userid"),item);
        });
        centerResult.forEach(item -> {
            if (ydacMap.containsKey(item.getStr("customer_id"))){
                //存在，看下到访时间是否一样，不一样则刷新
            } else {
                //不存在，需要补数，刷新到访时间
                ydacLessUsers.add(StrUtil.format("{}\t{}\t{}\t{}\t{}\t{}",
                        item.getStr("customer_id"),item.getStr("mobile"),
                        item.getStr("sale_team_id"),item.getStr("create_time"),
                        item.getStr("visit_first_date"),item.getStr("update_time")));
            }
        });

        System.out.println("移动案场需要补的数据：" + ydacLessUsers.size());
        ydacLessUsers.forEach(item -> {
            System.out.println(item);
        });
    }

    //测试加载数据
    @Test
    public void testLoadData() throws SQLException {
        List<Entity> centerResult = Db.use(DB_CENTER).query("select cr.agent_type_no,cr.agent_id,cr.report_date from customer_report cr\n" +
                "join (select max(t.id) maxId from customer_report t where t.report_date between '2022-03-20' and '2023-04-20' \n" +
                "and t.sale_team_id = '1000112' group by t.cus_tel) t on t.maxId = cr.id");

        System.out.println(centerResult.size());
    }

    @Test
    public void testLoadMsg() {
        String file = "D:\\temp\\12121217.txt";
        FileReader reader = new FileReader(file);
        List<String> msgs = reader.readLines();
        int i = 1;
        for (String msg : msgs) {
            System.out.println((i++) + ":" + JSONUtil.parseObj(msg));
        }
    }

    @Test
    public void genflushData() {
        String file = "D:\\移动案场\\数据导出\\客户中心生产（11.4-6号到访补数）.xls";
        ExcelReader reader = ExcelUtil.getReader(file);
        List<Map<String,Object>> msgs = reader.readAll();
        int i = 1;
        List<String> ydacUserSql = new ArrayList<>();

        for (Map<String,Object> msg : msgs) {
            System.out.println((i++) + ":" + JSONUtil.parseObj(msg));
            ydacUserSql.add(StrUtil.format("UPDATE dh_crm_saleruser u set u.createtime = '{}',u.lastmodify = '{}',u.award2time='{}',u.awardtime='{}' where userid = '{}';",
                    msg.get("create_time"),msg.get("update_time"),msg.get("visit_first_date"),msg.get("visit_first_date"),msg.get("customer_id")));
        }
        System.out.println("移动案场更新到访时间脚本：");
        ydacUserSql.forEach(System.out::println);
    }

    private CountDownLatch latch;
    /**
     * 1、获取所有的案场，按每个案场刷数据
     */
    @Test
    public void flushCustomerData() throws InterruptedException {

        ThreadPoolExecutor poolExecutor = ThreadUtil.newExecutor(6, 6);

        long start = System.currentTimeMillis();

        Set<String> saleTeamIds = getSaleTeamIds();
        latch = new CountDownLatch(saleTeamIds.size());
        String beginDate = "2022-03-03";
        String endDate = "2023-01-01";
        for (String saleTeamId : saleTeamIds) {
            poolExecutor.execute(new SaleTeamRunner(saleTeamId,beginDate,endDate));
        }
        log.info("等待所有案场刷数完成。。。。");
        latch.await();
        log.info("刷客户数据完成，耗时{}秒", (System.currentTimeMillis() - start) / 1000);
    }

    class SaleTeamRunner implements Runnable {

        private String saleTeamId;
        private String beginDate;
        private String endDate;

        public SaleTeamRunner(String saleTeamId, String beginDate, String endDate) {
            this.saleTeamId = saleTeamId;
            this.beginDate = beginDate;
            this.endDate = endDate;
        }

        @SneakyThrows
        public void run() {
            StringBuilder sqlBuilder = new StringBuilder();
            int total = 0;
            long start = System.currentTimeMillis();
            Page page = new Page(0, 100);
            PageResult<Entity> pageResult = null;
            Map<String, String> dictMap = null;
            while (pageResult == null || !pageResult.isLast()) {
                if (pageResult != null) {
                    page.setPageNumber(page.getPageNumber() + 1);
                }
                pageResult = Db.use(DB_CENTER).page("select * from customer_opportunity o where o.sale_team_id = ? and o.update_time between ? and ? order by o.update_time", page, saleTeamId, beginDate,endDate);
                log.info("当前案场{}，页码{}/{}，每页条数{}", saleTeamId, page.getPageNumber() + 1, pageResult.getTotalPage(), page.getPageSize());
                if (pageResult.isEmpty()) {
                    break;
                }
                if (dictMap == null) {
                    dictMap = getDictMap(saleTeamId);
                }
                total += pageResult.size();
                List<Object[]> argsList = new ArrayList<>();
                for (Entity entity : pageResult) {
                    processDictAttr(entity, dictMap, sqlBuilder, argsList);
                }
                Object[][] params = new Object[argsList.size()][];
                for (int i = 0; i < argsList.size(); i++) {
                    params[i] = argsList.get(i);
                }
                Db.use(DB_CENTER).executeBatch(sqlBuilder.toString(), params);
            }
            log.info("案场{}刷客户数据完成，总共更新{}条，耗时{}秒", saleTeamId,total, (System.currentTimeMillis() - start) / 1000);
            latch.countDown();
        }

        private void processDictAttr(Entity entity, Map<String, String> dictMap, StringBuilder sqlBuilder, List<Object[]> argsList) {
            boolean needBuildSql = sqlBuilder.length() == 0;
            List<Object> args = new ArrayList<>();
            if (needBuildSql) {
                sqlBuilder.append("update customer_opportunity set ");
            }
            List<String> ids = new ArrayList<>();
            List<String> names = new ArrayList<>();
            for (int i = 0;i< dictAttr.size();i++) {
                ids.clear();
                names.clear();
                DictAttr attr = FlushData.dictAttr.get(i);
                if (i > 0 && needBuildSql) {
                    sqlBuilder.append(" , ");
                }
                String id = null;
                String name = null;
                if (attr.getIdName() == null) {
                    if (needBuildSql) {
                        sqlBuilder.append(attr.getTextName()).append(" = ?");
                    }
                    //能找到字典说明是新数据，否则不更新
                    name = dictMap.getOrDefault(attr.getDictType()+entity.getStr(attr.getTextName()),entity.getStr(attr.getTextName()));
                } else {
                    if (needBuildSql) {
                        sqlBuilder.append(StrUtil.format("{} = ?,{} = ?",attr.getIdName(),attr.getTextName()));
                    }
                    id = entity.getStr(attr.getIdName());
                    name = entity.getStr(attr.getTextName());
                    //有种情况，就是ID和名称的值是互换的，比如target_acreage_name
                    String testId = id,testName = name;
                    if (StrUtil.isNotBlank(testId)) {
                        testId = testId.replaceAll(",","");
                    }
                    if (StrUtil.isNotBlank(testName)) {
                        testName = testName.replaceAll(",","");
                    }
                    if (!NumberUtil.isNumber(testId) && NumberUtil.isNumber(testName)) {
                        id = entity.getStr(attr.getTextName());
                        name = entity.getStr(attr.getIdName());
                    }
                    if ("is_important_name".equals(attr.getTextName())) {
                        if (StrUtil.equals(id,"0") && StrUtil.equals(name,"是")) {
                            id = "1";
                        }else if (StrUtil.equals(id,"1") && StrUtil.equals(name,"否")) {
                            id = "0";
                        } else if (StrUtil.isNotBlank(id) && StrUtil.isBlank(name)) {
                            id = "0".equals(id) ? "1" : "0";
                            name = dictMap.get(attr.getDictType()+id);
                        } else if (StrUtil.isNotBlank(name) && StrUtil.isBlank(id)) {
                            id = "是".equals(name) ? "1" : "0";
                        }
                    } else {
                        //如果原值没有，且有配迁移字段，则从迁移字段获取
                        if (StrUtil.isBlank(name) && attr.getFromName() != null) {
                            name = entity.getStr(attr.getFromName());
                        }
                        if (StrUtil.isNotBlank(id)) {
                            String[] tids = id.split(",");
                            for (String tid : tids) {
                                names.add(dictMap.getOrDefault(attr.getDictType()+tid,tid));
                            }
                            name = CollUtil.join(names,",");
                        } else if (StrUtil.isNotBlank(name)) {
                            String[] tnames = name.split(",");
                            for (String tname : tnames) {
                                String s = dictMap.get(attr.getDictType() + tname);
                                if (s != null) {
                                    ids.add(s);
                                }
                            }
                            id = CollUtil.join(ids,",");
                        }
                    }
                    //如果id超过32位，则截取下，不然数据库报错
                    if ("target_acreage_name".equals(attr.getTextName()) && StrUtil.isNotBlank(id) && id.length() > 32) {
                        id = id.substring(0,32);
                        log.warn("意向面积id过长，已截取为 {}",id);
                    }
                    args.add(id);
                }
                name = "null".equals(name) ? null : name;
                args.add(name);
            }
            if (needBuildSql) {
                sqlBuilder.append(" where id=? ");
            }
            args.add(entity.getBigInteger("id"));
            argsList.add(args.toArray());
        }
    }

    static List<DictAttr> dictAttr = getDictAttr();



    private static List<DictAttr> getDictAttr() {
        List<DictAttr> attrs = new ArrayList<>();
        attrs.add(new DictAttr("visiting_area","visiting_area_name","1101","liveplate"));
        attrs.add(new DictAttr("focus_id","focus_name","1005","buyfactor"));
        attrs.add(new DictAttr("living_num","living_num_name","1019","propcondition"));
        attrs.add(new DictAttr("customer_label_id","customer_label_name","1090","userrank"));
        attrs.add(new DictAttr("sales_resistance_id","sales_resistance_name","1087","notbuyreason"));

        attrs.add(new DictAttr("channel_way_id","channel_way_name","1006"));
        attrs.add(new DictAttr("age_stage","age_stage_name","1039"));
        attrs.add(new DictAttr("education_id","education_name","1010"));
        attrs.add(new DictAttr("trade_id","trade_name","1030"));
        attrs.add(new DictAttr("car_brand_id","car_brand_name","1029"));
        attrs.add(new DictAttr("profession_id","profession_name","1011"));
        attrs.add(new DictAttr("vehicle","vehicle_name","1027"));
        attrs.add(new DictAttr("family_member_id","family_member_name","1009"));
        attrs.add(new DictAttr("family_income_id","family_income_name","1026"));
        attrs.add(new DictAttr("target_unit_price","target_unit_price_name","1100"));
        attrs.add(new DictAttr("target_total_price","target_total_price_name","1017"));
        attrs.add(new DictAttr("target_objective","target_objective_name","1018"));
        attrs.add(new DictAttr("target_acreage","target_acreage_name","1014"));
        attrs.add(new DictAttr("work_area","work_area_name","1109"));
        attrs.add(new DictAttr("car_park_id","car_park_name","1028"));

        attrs.add(new DictAttr("is_importance","is_important_name","Important"));
        attrs.add(new DictAttr(null,"is_limited_loan","LimitedLoan"));
        attrs.add(new DictAttr(null,"is_limited_buy","LimitedBuy"));

        return attrs;
    }

    @AllArgsConstructor
    @Data
    static class DictAttr{
        private String idName;
        private String textName;
        private String dictType;
        private String fromName;//从哪个字段迁过来

        public DictAttr(String idName, String textName, String dictType) {
            this.idName = idName;
            this.textName = textName;
            this.dictType = dictType;
        }
    }

    private static Map<String, String> getDictMap(String saleTeamId) throws SQLException {
        List<String> saleOrgList = saleTeamSaleOrgMap.get(saleTeamId);
        List<Entity> ydacResult = Db.use(DB_YDAC).query("select id,name,dict_type_id,sale_org_id from  mkt_dict where sale_org_id= ? and FLAG = 0",saleOrgList.get(0));
        if (ydacResult.isEmpty()) {
            throw new RuntimeException("无法根据案场id获取到字典数据：" + saleTeamId);
        }
        Map<String,String> dictMap = new HashMap<>();
        for (Entity dict : ydacResult) {
            if (dictMap.containsKey(dict.getStr("dict_type_id") + dict.getStr("name"))) {
                log.info("字典名称重复：{}",dict);
            }
            if (dictMap.containsKey(dict.getStr("dict_type_id") + dict.getStr("id"))) {
                log.info("字典ID重复：{}",dict);
            }
            dictMap.put(dict.getStr("dict_type_id") + dict.getStr("name"),dict.getStr("id"));
            dictMap.put(dict.getStr("dict_type_id") + dict.getStr("id"),dict.getStr("name"));
        }

        dictMap.put("LimitedBuy是","限购");
        dictMap.put("LimitedBuy否","不限购");
        dictMap.put("LimitedLoan是","限贷");
        dictMap.put("LimitedLoan否","不限贷");
        dictMap.put("Important1","是");
        dictMap.put("Important0","否");
        return dictMap;
    }

    private static Map<String,List<String>> saleTeamSaleOrgMap = new HashMap<>();

    @SneakyThrows
    private Set<String> getSaleTeamIds() {
        List<Entity> query = Db.use(DB_CENTER).query("select r.sale_team_id,r.sale_org_id  from sale_team_sale_org_rel r where r.deleted = 0");
        for (Entity entity : query) {
            String sale_team_id = entity.getStr("sale_team_id");
            List<String> saleOrgs = saleTeamSaleOrgMap.get(sale_team_id);
            if (saleOrgs == null) {
                saleOrgs = new ArrayList<>();
                saleTeamSaleOrgMap.put(sale_team_id,saleOrgs);
            }
            saleOrgs.add(entity.getStr("sale_org_id"));
        }
        Set<String> saleTeamIds = new HashSet<>(saleTeamSaleOrgMap.keySet());

        log.info("共找到案场数量 {} 个",saleTeamIds.size());
//        saleTeamIds.clear();
//        saleTeamIds.add("1000121");
        return saleTeamIds;
    }

}
